package cj.web.admin.dao;
/*
 *  
 *  
*/
import java.util.List;
import java.util.Map;

import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.SQL;
import net.paoding.rose.jade.annotation.SQLParam;
import cj.web.admin.domain.KDBookingCard;

/**
 * 
 * @author 
 *
 */
@DAO
public interface KDBookingCardDAO{
	
	/*@SQL("SELECT a.id,a.account,a.pwd,a.beginAt,a.link,a.phone,a.city,a.area,a.street,a.address,a.status,a.memo,a.deleteFlag,a.createAT,a.updateAT,b.paperCardName,b.paperName,b.months FROM KDBookingCard a left join KDCard b on a.account=b.cardNum WHERE a.deleteFlag=1 #if(:t.account!=''){ and a.account like '%##(:t.account)%' }"
			+ "#if(:t.area!=null){ and a.area =:t.area } "
			+ "#if(:t.street!=null){ and a.street =:t.street } "
			+ "#if(:t.address!=''){ and a.address like '%##(:t.address)%' } "
			+ " ORDER BY a.updateAt DESC LIMIT :t.start,:t.length ")
    List<Map> query(@SQLParam("t") KDBookingCard kDBookingCard);*/
	
	@SQL("SELECT a.id,a.account,a.inType,a.pwd,a.beginAt,a.link,a.phone,a.city,a.area,a.street,a.address,a.status,a.memo,a.deleteFlag,a.createAT,a.updateAT,b.paperCardName,b.paperName,b.months "
			+ "FROM ( select * FROM KDBookingCard WHERE deleteFlag=1 #if(:t.account!=''){ and account like '%##(:t.account)%' } "
			+ "#if(:t.area!=null){ and area =:t.area } "
			+ "#if(:t.street!=null){ and street =:t.street } "
			+ "#if(:t.address!=''){ and address like '%##(:t.address)%' } "
			+ " ORDER BY updateAt DESC LIMIT :t.start,:t.length ) a left join KDCard b on a.account=b.cardNum ORDER BY a.updateAt DESC")
    List<Map> query(@SQLParam("t") KDBookingCard kDBookingCard);
	
	@SQL("SELECT count(1) FROM KDBookingCard WHERE deleteFlag=1 #if(:t.account!=''){ and account like '%##(:t.account)%' } "
			+ "#if(:t.area!=null){ and area =:t.area } "
			+ "#if(:t.street!=null){ and street =:t.street } "
			+ "#if(:t.address!=''){ and address like '%##(:t.address)%' } ")
    int querySzie(@SQLParam("t") KDBookingCard kDBookingCard);
    
    @SQL("select * from KDBookingCard where id=:1")
    KDBookingCard queryById(int id);
    
    @SQL("UPDATE KDBookingCard SET memo=:2 WHERE id=:1")
    void updateMemoById(int id,String memo);
    
    @SQL("UPDATE KDBookingCard SET status=1 WHERE id=:1")
    void updateStatusById(int id);
    
    @SQL("UPDATE KDBookingCard SET deleteFlag=0 WHERE id=:1")
    void deleteById(int id);
    
    //导出数据
    @SQL("SELECT a.id,a.account,a.pwd,a.beginAt,a.link,a.phone,a.city,a.area,a.street,a.address,a.status,a.memo,a.deleteFlag,a.createAT,a.updateAT,b.paperName,b.paperCardName,b.months FROM KDBookingCard a LEFT JOIN KDCard b on a.account=b.cardNum WHERE a.deleteFlag = 1 #if(:start!=''){ and a.createAT>=:start } #if(:end!=''){ and a.createAT<=:end } order by a.id")
    List<Map> output(@SQLParam("start")String start, @SQLParam("end")String end);

    //根据id批量更改状态
    @SQL("UPDATE KDBookingCard SET status=1 WHERE id in (:1)")
	void updateStatusByIds(List<Integer> ids);
    
}